from python_baway.review.review_2021_02_18Thu.mysql_oop.MysqlHelper import MysqlHelper
from python_baway.common.xdb import xdb_args
from python_baway.review.review_2021_02_18Thu.birthdays.xrandom_names_and_birthdays import xget_persons_names_and_birthdays
import datetime
from python_baway.common.xcommon import xshow_table
import random

random.seed(1)
xconn = MysqlHelper(**xdb_args)
xconn.connect()
xtable = 'persons_review_2021_02_18Thu'
xconn.exec(f'DROP TABLE IF EXISTS {xtable}')
xconn.exec(f'''\
CREATE TABLE IF NOT EXISTS {xtable}
(
    id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name varchar(64) NOT NULL UNIQUE,
    gender enum('f', 'm', '?') NOT NULL DEFAULT '?',
    citizen_id char(18) NOT NULL,
    birthday date NULL
)
DEFAULT CHARACTER SET = utf8mb4
DEFAULT COLLATE = utf8mb4_general_ci
''')

xpersons = xget_persons_names_and_birthdays(6, 4,
                                            datetime.datetime(1990, 1, 1, 0, 0, 0),
                                            datetime.datetime(2006, 2, 18, 0, 0, 0))
xfragments = []
for xname, xbirthday, xgender, xid in xpersons:
    xfragments.append(f"('{xname}', '{xgender}', '{xid}', '{xbirthday.strftime('%Y-%m-%d')}')")
xcaluses = ', '.join(xfragments)
xconn.exec(f'''\
INSERT INTO {xtable}
(name, gender, citizen_id, birthday) VALUES
{xcaluses}''')
xconn.commit()

xrows, xrows_cnt = xconn.query(f'SELECT * FROM {xtable}')
print(xrows_cnt)
xshow_table(xrows)

xconn.close()
